* QCEW_sunset_merge.do
* 2014.02.07
* Last update 2014.02.07: script created
* Merges sunsets onto QCEW data 

capture log close
set more off
timer clear 1
timer on 1
clear
set matsize 10000

local work "/DIRECTORY"

log using "`work'/logs/QCEW_sunset_merge.log", replace

* Reading data
use "`work'/data/qcew/QCEW_raw_quarterly.dta", clear

* Merging quarterly avg sunsets
merge 1:1 fips tq using "`work'/data/qcew/Quarterly_county_sunsets.dta", keep(1 3) nogenerate

* Merging coastal distance
merge m:1 fips using "`work'/data/gis/Stata inputs/CntyCoastDistances.dta", keep(1 3) nogenerate

* Merging demographics from Census counties DB
merge m:1 fips using "`work'/data/counties db/CountiesDB_subset.dta", keep(1 3) nogenerate

* Labels
label variable AverageWeeklyWage10 "Weekly wage"
label variable AverageWeeklyWage101 "Weekly wage - goods"
label variable AverageWeeklyWage102 "Weekly wage - services"
label variable sunset_time "Sunset time"

* Additional variables
encode state, generate(statecode)
encode modal_time_zone, generate(tzcode)
gen date=dofq(tq)
gen year = year(date)
gen quarter = quarter(date)
egen lat_cut_freq = cut(latitude), group(5)
gen lat_round = round(latitude, 1)
gen filler = 1
foreach naics in 10 101 1011 1012 1013 102 1021 1022 1023 1024 1025 1026 1027 1029 {
	gen ln_avwage`naics' = log(AverageWeeklyWage`naics')
}
bysort fips year: egen sunset_time_avg = mean(sunset_time)

* Merging in CBSA/MSA identifiers (NBER crosswalk)
gen fipscounty=fips
merge m:1 fipscounty using "`work'/data/cbsatocountycrosswalk.dta", keep(1 3)	
drop fipscounty
gen CHI = (cbsa=="16974")
gen NYC = (cbsa=="35644")
gen LA = (cbsa=="31084")
gen notCBSA = missing(cbsaname)

* Aligning demographics with ATUS-based variables
foreach x in female black white asian other {
	gen pct_`x' = .
	replace pct_`x' = pct_`x'_2000 if inrange(year, 1990, 2005)
	replace pct_`x' = pct_`x'_2010 if inrange(year, 2006, 2015)
}
gen median_age = .
replace median_age = median_age_2000 if inrange(year, 1990, 2005)
replace median_age = median_age_2010 if inrange(year, 2006, 2015)
egen med_age_3=cut(median_age), group(3)
gen ln_pop_density = log(pop_density)

* Constant SST
gen SST1990 = sunset_time_avg if year==1990
sort fips year
bysort fips: replace SST1990 = SST1990[_n-1] if missing(SST1990)

* Saving
save "`work'/data/qcew/QCEW_master.dta", replace








timer off 1
timer list 1
capture log close



